﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace PatientsManagement.Database
{
    static class PatientsData
    {
        static PatientsDataContext patients = new PatientsDataContext();
        
        public class PatientsInformation
        {
            public int ID { get; set; }
            public string Imię { get; set; }
            public string Nazwisko { get; set; }
            public string Miasto { get; set; }
            public string Ulica { get; set; }
            public string NrDomu { get; set; }
            public string Kod_Pocztowy { get; set; }
            public string Pesel { get; set; }
        }

        public static IQueryable<PatientsInformation> GetPatientsTable()
        {
            var query = (from patient in patients.Patients
                         select new PatientsInformation()
                         {
                             ID = patient.PatientID,
                             Imię = patient.Forename,
                             Nazwisko = patient.Surname,
                             Miasto = patient.City,
                             Ulica = patient.Street,
                             NrDomu = patient.HouseNumber,
                             Kod_Pocztowy = patient.PostalCode,
                             Pesel = patient.Pesel
                         });
            return query;
        }

        

        internal static void AddPatient(string forename, string surname, string homeTown, string street, string houseNumber, string postalCode, string pesel)
        {
            Patient patient = new Patient
            {
                Forename = forename,
                Surname = surname,
                City = homeTown,
                Street = street,
                HouseNumber = houseNumber,
                PostalCode = postalCode,
                Pesel = pesel
            };
            patients.Patients.InsertOnSubmit(patient);
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static void EditPatient(string id,string forename, string surname, string homeTown, string street, string houseNumber, string postalCode, string pesel)
        {
            var singlePatient = (from patient in patients.Patients
                              where patient.PatientID == int.Parse(id)
                              select patient).Single();
            singlePatient.Forename = forename;
            singlePatient.Surname = surname;
            singlePatient.City = homeTown;
            singlePatient.Street = street;
            singlePatient.HouseNumber = houseNumber;
            singlePatient.PostalCode = postalCode;
            singlePatient.Pesel = pesel;
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static void DeletePatient(string id)
        {
            var singlePatient = (from patient in patients.Patients
                              where patient.PatientID == int.Parse(id)
                              select patient).Single();
            patients.Patients.DeleteOnSubmit(singlePatient);
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public class VisitsInformation
        {
            public int ID_Wizyty { get; set; }
            public string Pacjent { get; set; }
            public string Lekarz { get; set; }
            public string Data_Wizyty { get; set; }
        }

        public static IQueryable<VisitsInformation> GetPatientVisitsTable()
        {
            var query = (from visit in patients.Visits
                         join patient in patients.Patients on visit.PatientID equals patient.PatientID into pd
                         join doctor in patients.Doctors on visit.DoctorID equals doctor.DoctorID into dd
                         from x in pd.DefaultIfEmpty()
                         from y in dd.DefaultIfEmpty()
                         select new VisitsInformation()
                         {
                             ID_Wizyty = visit.VisitID,
                             Pacjent = (x.Forename + " " + x.Surname)== null ? "" : (x.Forename + " " + x.Surname),
                             Lekarz = y.Forename + " " + y.Surname,
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         });
            return query;
        }

        public class PrescriptionsInformation
        {
            public int ID_Recepty { get; set; }
            public int ID_Wizyty { get; set; }
            public string Pacjent { get; set; }
            public string Lekarz { get; set; }
            public string Data_Wizyty { get; set; }
        }
        internal static IQueryable<PrescriptionsInformation> GetPrescriptionsTable()
        {
            var query = (from prescription in patients.Prescriptions
                         join visit in patients.Visits on prescription.VisitID equals visit.VisitID
                         join patient in patients.Patients on visit.PatientID equals patient.PatientID into pd
                         join doctor in patients.Doctors on visit.DoctorID equals doctor.DoctorID into dd
                         from patientsData in pd.DefaultIfEmpty()
                         from doctorData in dd.DefaultIfEmpty()
                         select new PrescriptionsInformation()
                         {
                             ID_Recepty = prescription.PrescriptionID,
                             ID_Wizyty = visit.VisitID,
                             Pacjent = patientsData.Forename + " " + patientsData.Surname,
                             Lekarz = doctorData.Forename + " " + doctorData.Surname,
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         });
            return query;
        }

        public class DiagnosisInformation
        {
            public int ID_Diagnozy { get; set; }
            public int ID_Wizyty { get; set; }
            public string Pacjent { get; set; }
            public string Lekarz { get; set; }
            public string Nazwa_Diagnozy { get; set; }
            public string Data_Wizyty { get; set; }
        }

        public static IQueryable<DiagnosisInformation> GetDiagnosisTable()
        {
            var query = (from diagnosis in patients.DiagnosisDatas
                         join names in patients.Diagnosis on diagnosis.DiagnosisID equals names.DiagnosisID
                         join visit in patients.Visits on diagnosis.VisitID equals visit.VisitID
                         join patient in patients.Patients on visit.PatientID equals patient.PatientID into pd
                         join doctor in patients.Doctors on visit.DoctorID equals doctor.DoctorID into dd
                         from patientsData in pd.DefaultIfEmpty()
                         from doctorData in dd.DefaultIfEmpty()
                         select new DiagnosisInformation()
                         {
                             ID_Diagnozy = diagnosis.DiagnosisID,
                             ID_Wizyty = visit.VisitID,
                             Pacjent = patientsData.Forename + " " + patientsData.Surname,
                             Lekarz = doctorData.Forename + " " + doctorData.Surname,
                             Nazwa_Diagnozy = names.DiagnosisName,
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         });
            return query;
        }
    }
}
